package com.swordget.commons;

import com.alibaba.druid.pool.DruidDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.*;

/**
 * @Author: David
 * @Date: Created in 15:48 2020/3/27
 * @Description: jdbc工具类
 * @Modified By:
 * @Version: 0.0.1
 */
@Component
public class JdbcUtil {

    @Autowired
    private DruidDataSource dataSource;

    private final static Logger LOG = LoggerFactory.getLogger(JdbcUtil.class);

    public Object findOne(String sql) {
        try(Connection conn = dataSource.getConnection();
            Statement statement = conn.createStatement();
            ResultSet resultSet = statement.executeQuery(sql)) {
            if (resultSet.next()) {
                return resultSet.getObject(1);
            }
        } catch (SQLException e) {
            LOG.error("程序执行过程中出现异常", e);
        }
        return null;
    }

    public boolean executeBatch(String ...ages) {
        return executeBatch(Arrays.asList(ages));
    }

    /**
     * 批量执行SQL语句
     * @param commands 需要批量执行的SQL语句
     * @return
     * @throws SQLException
     */
    public boolean executeBatch(List<String> commands) {
        Connection conn = null;
        Statement statement = null;
        try {
            conn = dataSource.getConnection();
            statement = conn.createStatement();
            conn.setAutoCommit(false);
            for (String command : commands) {
                LOG.info(command);
                if (statement.executeUpdate(command) < 0) {
                    throw new SQLException();
                }
            }
            conn.commit();
            return true;
        } catch (SQLException e) {
            try {
                if (null != conn) {
                    conn.rollback();
                }
            } catch (SQLException ex) {
                LOG.error("程序执行过程中出现异常", e);
            }
        } finally {
            if (null != statement) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    LOG.error("程序执行过程中出现异常", e);
                }
            }
            if (null != conn) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    LOG.error("程序执行过程中出现异常", e);
                }
            }
        }
        return false;
    }

    /**
     * 执行单条命令
     * @param command 要执行的sql语句
     * @return
     * @throws SQLException
     */
    public boolean execute(String command) {
        try(Connection conn = dataSource.getConnection();
            Statement statement = conn.createStatement();) {
            try {
                conn.setAutoCommit(false);
                LOG.info(command);
                statement.execute(command);
                conn.commit();
                return true;
            } catch (SQLException e) {
                try {
                    conn.rollback();
                } catch (SQLException e1) {
                    throw e1;
                }
            }
        } catch (SQLException e) {
            LOG.error("程序执行过程中出现异常", e);
        }
        return false;
    }

    public boolean create(String command) {
        Connection conn = null;
        Statement statement = null;
        try {
            conn = dataSource.getConnection();
            statement = conn.createStatement();
            statement.execute(command);
            return true;
        } catch (SQLException e) {
            LOG.error("程序执行过程中出现异常", e);
        } finally {
            if (null != statement) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    LOG.error("程序执行过程中出现异常", e);
                }
            }
            if (null != conn) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    LOG.error("程序执行过程中出现异常", e);
                }
            }
        }
        return false;
    }

    public int executeCount(String command) {
        try(Connection conn = dataSource.getConnection();
            Statement statement = conn.createStatement();
            ResultSet resultSet = statement.executeQuery(command)){
            LOG.info(command);
            if (resultSet.next()) {
                return resultSet.getInt(1);
            }
        } catch (SQLException e) {
            LOG.error("程序执行过程中出现异常", e);
        }
        return 0;
    }

    public boolean executeUpdate(String command) {
        try(Connection conn = dataSource.getConnection();
            Statement statement = conn.createStatement()){
            LOG.info(command);
            return statement.executeUpdate(command) > 0;
        } catch (SQLException e) {
            LOG.error("程序执行过程中出现异常", e);
        }
        return false;
    }

    public List<String> findOneByList(String command) {
        List<String> resultList = new ArrayList();
        try(Connection conn = dataSource.getConnection();
            Statement statement = conn.createStatement();
            ResultSet resultSet = statement.executeQuery(command)) {
            while (resultSet.next()) {
                resultList.add(resultSet.getString(1));
            }
        } catch (SQLException e) {
            LOG.error("程序执行过程中出现异常", e);
        }
        return resultList;
    }

    public Map<Integer, List<String>> findAll(String command) {
        Map<Integer, List<String>> map = new HashMap<>();
        LOG.info(command);
        try(Connection conn = dataSource.getConnection();
            Statement statement = conn.createStatement();
            ResultSet resultSet = statement.executeQuery(command)) {
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            int rowNum = 1;
            while (resultSet.next()) {
                List<String> resultList = new ArrayList(columnCount);
                for (int count = 1; count <= columnCount; count++) {
                    resultList.add(resultSet.getString(count));
                }
                map.put(rowNum++, resultList);
            }
        } catch (SQLException e) {
            LOG.error("程序执行过程中出现异常", e);
        }
        return map;
    }
}
